﻿using Aspose.Cells;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Diagnostics;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ExcelCompare
{
    public partial class Form1 : Form
    {
        private List<String> ErpTitle = new List<string>();
        private List<String> PlatTitle = new List<string>();
        private DataTable ErpDataSource = null;
        private DataTable PlatDataSource = null;
        public Form1()
        {
            InitializeComponent();
            String filter = ConfigurationManager.AppSettings["filter"];
            if (filter == null || filter == "")
            {
                this.orderSourceValue.Items.Add("请选择！");
            }
            else
            {
                foreach (String str in filter.Split('|'))
                {
                    this.orderSourceValue.Items.Add(str);
                }
            }
            this.orderSourceValue.SelectedIndex = 0;
        }

        //读取Excel文件按钮
        private void ErpBut_Click(object sender, EventArgs e)
        {
            string excelFilePath = GetSelectPath();
            if (excelFilePath != "")
            {
                ReadExcelFile((Button)sender, excelFilePath);
            }
        }

        //读取平台文件按钮
        private void PlatBut_Click(object sender, EventArgs e)
        {
            string excelFilePath = GetSelectPath();
            if (excelFilePath != "")
            {
                ReadExcelFile((Button)sender, excelFilePath);
            }
        }

        //比较按钮
        private void CompareBut_Click(object sender, EventArgs e)
        {
            Button button = (Button)sender;
            //获取选择的变量
            int erpOrderColumnIndex = erpOrderColumn.Items.IndexOf(erpOrderColumn.Text);
            int erpAmountColumnIndex = erpAmountColumn.Items.IndexOf(erpAmountColumn.Text);
            int platOrderColumnIndex = platOrderColumn.Items.IndexOf(platOrderColumn.Text);
            int platAmountColumnIndex = platAmountColumn.Items.IndexOf(platAmountColumn.Text);
            int orderSourceColumnIndex = orderSourceColumn.Items.IndexOf(orderSourceColumn.Text);
            String erpOrderSource = orderSourceValue.Text;
            bool calcDiff = CalcDiff.Checked;

            HashSet<String> erpOnly = new HashSet<string>();
            HashSet<String> platOnly = new HashSet<string>();

            //ERP数据源判断
            if (ErpDataSource == null)
            {
                MessageBox.Show("请选择Erp数据源！");
                return;
            }

            if (erpOrderColumnIndex == -1)
            {
                MessageBox.Show("请选择Erp数据源的[平台订单列]！");
                return;
            }

            if (erpAmountColumnIndex == -1)
            {
                MessageBox.Show("请选择Erp数据源的[金额列]！");
                return;
            }


            //平台数据源判断
            if (PlatDataSource == null)
            {
                MessageBox.Show("请选择[" + erpOrderSource + "]数据源！");
                return;
            }

            if (platOrderColumnIndex == -1)
            {
                MessageBox.Show("请选择平台数据源的[平台订单列]！");
                return;
            }

            if (platAmountColumnIndex == -1)
            {
                MessageBox.Show("请选择平台数据源的[金额列]！");
                return;
            }

            //订单来源判断
            if (orderSourceColumnIndex == -1)
            {
                MessageBox.Show("请选择过滤ERP的[订单来源列]！");
                return;
            }

            if (erpOrderSource.StartsWith("请选择"))
            {
                MessageBox.Show("请选择过滤ERP的订单来源值！");
                return;
            }

            Stopwatch stopwatch = new Stopwatch();
            stopwatch.Start();
            button.Enabled = false;

            //过滤ERP数据源
            foreach (DataRow dataRow in ErpDataSource.Rows)
            {
                if (dataRow[orderSourceColumnIndex].ToString() == erpOrderSource)
                    erpOnly.Add(dataRow[erpOrderColumnIndex] + "," + dataRow[erpAmountColumnIndex]);
            }

            //平台唯一值
            foreach (DataRow dataRow in PlatDataSource.Rows)
            {
                platOnly.Add(dataRow[platOrderColumnIndex] + "," + dataRow[platAmountColumnIndex]);
            }

            //差集处理
            HashSet<String> erpOnlyCopy = new HashSet<string>(erpOnly);
            erpOnly.ExceptWith(platOnly);
            platOnly.ExceptWith(erpOnlyCopy);

            //创建Sheet
            Workbook wb = new Workbook();
            wb.Worksheets.Add();

            //写入 ERP与平台差异 
            Worksheet erpSheet = wb.Worksheets[0];
            erpSheet.Name = "ERP与平台差异";
            Cells writeErp = erpSheet.Cells;

            int rowIndex = 1;
            if (erpOnly.Count > 0)
            {
                int titleIndex = 0;
                for (; titleIndex < ErpTitle.Count; titleIndex++)
                {
                    writeErp[0, titleIndex].PutValue(ErpTitle[titleIndex]);
                }
                if (calcDiff)
                    writeErp[0, titleIndex].PutValue("差异");
                foreach (DataRow dataRow in ErpDataSource.Rows)
                {
                    if (dataRow[orderSourceColumnIndex].ToString() == erpOrderSource && erpOnly.Contains(dataRow[erpOrderColumnIndex] + "," + dataRow[erpAmountColumnIndex]))
                    {
                        int columnIndex = 0;
                        for (; columnIndex < dataRow.ItemArray.Length; columnIndex++)
                        {
                            writeErp[rowIndex, columnIndex].PutValue(dataRow[columnIndex]);
                        }
                        if (calcDiff)
                        { //计算金额差异
                            DataRow[] filterRows = PlatDataSource.Select("Column" + (platOrderColumnIndex + 1) + "='" + dataRow[erpOrderColumnIndex] + "'");
                            if (filterRows.Length > 0)
                            {
                                writeErp[rowIndex, columnIndex].PutValue(Convert.ToDecimal(filterRows[0][platAmountColumnIndex]) - Convert.ToDecimal(dataRow[erpAmountColumnIndex]));
                            }
                            else
                                writeErp[rowIndex, columnIndex].PutValue("不包含");
                        }
                        rowIndex++;
                    }
                }
            }

            //写入 平台与ERP差异
            Worksheet platSheet = wb.Worksheets[1];
            platSheet.Name = "平台与ERP差异";
            Cells writePlat = platSheet.Cells;

            rowIndex = 1;
            if (platOnly.Count > 0)
            {
                for (int titleIndex = 0; titleIndex < PlatTitle.Count; titleIndex++)
                {
                    writePlat[0, titleIndex].PutValue(PlatTitle[titleIndex]);
                }
                foreach (DataRow dataRow in PlatDataSource.Rows)
                {
                    if (platOnly.Contains(dataRow[platOrderColumnIndex] + "," + dataRow[platAmountColumnIndex]))
                    {
                        for (int columnIndex = 0; columnIndex < dataRow.ItemArray.Length; columnIndex++)
                        {
                            writePlat[rowIndex, columnIndex].PutValue(dataRow[columnIndex]);
                        }
                        rowIndex++;
                    }
                }
            }

            //保存
            String fileName = Application.StartupPath + "\\CompareResult\\" + DateTime.Now.ToString("yyyy-MM-dd HHmmss") + ".xlsx";
            wb.Save(fileName);
            stopwatch.Stop();
            button.Enabled = true;
            Process.Start(fileName);
            MessageBox.Show("比较成功,共耗时[" + Math.Round(stopwatch.Elapsed.TotalSeconds, 2) + "]秒！");
        }

        /// <summary>
        /// 获取选择文件路径 
        /// </summary>
        /// <returns></returns>
        private string GetSelectPath()
        {
            OpenFileDialog openFileDialog = new OpenFileDialog
            {
                Filter = "支持的文件|*.xls;*.xlsx|高版本Excel文件|*.xlsx|低版本Excel文件|*.xls",
                RestoreDirectory = true
            };
            if (openFileDialog.ShowDialog() == DialogResult.OK)
            {
                return openFileDialog.FileName;
            }
            return "";
        }

        /// <summary>
        /// 读取Excel文件
        /// </summary>
        private void ReadExcelFile(Button button, String excelFilePath)
        {
            new Task(() =>
            {
                this.BeginInvoke(new MethodInvoker(() =>
                {
                    button.Text = "正在读取请稍后.";
                    button.Enabled = false;
                }));

                Stopwatch stopwatch = new Stopwatch();
                stopwatch.Start();
                Workbook workbook = new Workbook(excelFilePath);
                Cells cells = workbook.Worksheets[0].Cells;
                if (button.Name.StartsWith("Erp"))
                {
                    //读取数据
                    //ErpDataSource = cells.ExportDataTable(0, 0, cells.MaxDataRow + 1, cells.MaxColumn + 1);
                    ErpDataSource = cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxColumn + 1);
                    //处理表头
                    this.BeginInvoke(new MethodInvoker(() =>
                    {
                        DataRow dataRow = ErpDataSource.Rows[0];
                        ErpTitle.Clear();
                        erpOrderColumn.Items.Clear();
                        erpAmountColumn.Items.Clear();
                        orderSourceColumn.Items.Clear();
                        for (int headerColumnIndex = 0; headerColumnIndex < dataRow.ItemArray.Length; headerColumnIndex++)
                        {
                            ErpTitle.Add(dataRow[headerColumnIndex].ToString());
                            erpOrderColumn.Items.Add(dataRow[headerColumnIndex]);
                            erpAmountColumn.Items.Add(dataRow[headerColumnIndex]);
                            orderSourceColumn.Items.Add(dataRow[headerColumnIndex]);
                        }
                        ErpDataSource.Rows[0].Delete();
                    }));
                }
                else
                {
                    PlatDataSource = cells.ExportDataTable(0, 0, cells.MaxDataRow + 1, cells.MaxColumn + 1);
                    PlatDataSource = cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxColumn + 1);
                    this.BeginInvoke(new MethodInvoker(() =>
                    {
                        DataRow dataRow = PlatDataSource.Rows[0];
                        PlatTitle.Clear();
                        platOrderColumn.Items.Clear();
                        platAmountColumn.Items.Clear();
                        for (int headerColumnIndex = 0; headerColumnIndex < dataRow.ItemArray.Length; headerColumnIndex++)
                        {
                            PlatTitle.Add(dataRow[headerColumnIndex].ToString());
                            platOrderColumn.Items.Add(dataRow[headerColumnIndex]);
                            platAmountColumn.Items.Add(dataRow[headerColumnIndex]);
                        }
                        PlatDataSource.Rows[0].Delete();
                        //实际Excel的列名不是DataTable的列名
                        //MessageBox.Show("表名:" + PlatDataSource.TableName);
                        //for (int i = 0; i < PlatDataSource.Columns.Count; i++)
                        //{
                        //    MessageBox.Show("第[" + i + "]列名为:" + PlatDataSource.Columns[i].ColumnName);
                        //}
                    }));
                }
                stopwatch.Stop();

                this.BeginInvoke(new MethodInvoker(() =>
                {
                    button.Text = "共[" + cells.MaxRow + "]条记录\n读取耗时[" + Math.Round(stopwatch.Elapsed.TotalSeconds, 2) + "]秒";
                    button.Enabled = true;
                }));

            }).Start();
        }
    }
}
